package com.hhit.collaborative_learning.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import com.hhit.collaborative_learning.dao.BBS_ClassDao;
import com.hhit.collaborative_learning.dao.BBS_TopicDao;
import com.hhit.collaborative_learning.utils.StringUtil;
import com.mysql.jdbc.Statement;
import com.mysql.jdbc.StringUtils;

public class BBS_TopicDaoImpl implements BBS_TopicDao {

	@Override
	public int getcount(Connection con, String find) {
		// TODO Auto-generated method stub
		int num = 0;
		String sql = "select count(*) from `collaborative_learning`.`bbs_topic`";
		if (!StringUtils.isNullOrEmpty(find)) {
			sql += " where " + find + " ";
		}
		try {
			PreparedStatement prs = con.prepareStatement(sql);
			ResultSet res = prs.executeQuery();
			if (res.next()) {
				num = res.getInt(1);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return num;
	}

	@Override
	public ResultSet query(Connection con, int start, int end,
			String findwhate, String findby, String order, String sort) {
		// TODO Auto-generated method stub
		String sql = "SELECT bt.*,u.*,btl.Name as stateName FROM collaborative_learning.bbs_topic as bt left  join  collaborative_learning.user as u on u.userId=bt.UserID left join collaborative_learning.bbs_topiclabel as btl on btl.ID=bt.TopicLabelID";
		if (!StringUtils.isNullOrEmpty(findwhate)
				&& !StringUtils.isNullOrEmpty(findby)) {
			sql += " where " + findwhate + " " + findby + " ";
		}
		if (!StringUtils.isNullOrEmpty(sort)) {
			sql += " order by " + sort + " ";
			if (!StringUtils.isNullOrEmpty(order)) {
				sql += " " + order;
			}
		}
		if (end != 0 && start < end) {
			sql += " limit " + start + "," + end;
		}
		PreparedStatement prs;
		try {
			prs = con.prepareStatement(sql);
			return prs.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("非法请求：" + sql);
			return null;
		}
	}

	@Override
	public ResultSet queryOther(Connection con, int start, int end,
			String classid, String label, String state, String order,
			String sort) {
		// TODO Auto-generated method stub
		String sql = "select t.*,b.StateName,tl.Name as labelName,uc.userNickName as CreatorName,mu.userNickName as ModifierName, ure.userNickName as RepliedName from `collaborative_learning`.`bbs_topic` as t left join(select ts.TopicID,GROUP_CONCAT(tst.Name SEPARATOR ' ') as StateName from `collaborative_learning`.`bbs_topicstate` as ts left join `collaborative_learning`.`bbs_topicstatetype` as tst on tst.ID=ts.StateTypeID group by ts.TopicID) as b on b.TopicID = t.ID LEFT join `collaborative_learning`.`bbs_topiclabel` as tl on tl.ID=t.TopicLabelID left join `collaborative_learning`.`user` as uc on uc.userId=t.UserID left join `collaborative_learning`.`user` as mu on mu.userId=t.UserID left join `collaborative_learning`.`user` as ure on ure.userId=t.RepliedBy ";
		if (classid != null && !"".equals(classid)) {
			sql += " where t.ClassID=" + classid;
			if (state != null && !"".equals(state)) {
				sql += " and b.StateName " + state;
			}
			if (label != null && !"".equals(label)) {
				sql += " and tl.ID like " + label;
			}
		}
		if (sort != null && !"".equals(sort)) {
			sql += " order by " + sort + " ";
			if (order != null && !"".equals(order)) {
				sql += " " + order;
			}
		}
		if (end != 0 && start < end) {
			sql += " limit " + start + "," + end;
		}
		PreparedStatement prs;
		try {
			prs = con.prepareStatement(sql);
			System.out.println("请求：" + sql);
			return prs.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("非法请求：" + sql);
			return null;
		}
	}

	@Override
	public ResultSet queryforSearch(Connection con, int start, int end,
			String find, String order, String sort) {
		// TODO Auto-generated method stub
		String sql = "SELECT bt.ID,bt.UserID,bt.Title,bt.CreatedOn,bt.Hit,u.userNickName FROM collaborative_learning.bbs_topic as bt left join collaborative_learning.user as u on u.userId=bt.UserID ";
		if (!StringUtils.isNullOrEmpty(find)) {
			sql += "where bt.Title like '%" + find
					+ "%' or u.userNickName like '%" + find + "%' ";
		}
		if (!StringUtils.isNullOrEmpty(sort)) {
			sql += " order by " + sort + " ";
			if (order != null) {
				sql += " " + order;
			}
		}
		if (end != 0 && start < end) {
			sql += " limit " + start + "," + end;
		}
		PreparedStatement prs;
		try {
			prs = con.prepareStatement(sql);
			return prs.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("非法请求：" + sql);
			return null;
		}
	}

	@Override
	public int delet(Connection con, String findby, String ids) {
		// TODO Auto-generated method stub
		try {
			String sql = "DELETE FROM `collaborative_learning`.`bbs_topic` WHERE "
					+ findby + " in (" + ids + ")";
			PreparedStatement prs = con.prepareStatement(sql);
			return prs.executeUpdate();
		} catch (SQLException ex) {
			ex.printStackTrace();
			return 0;
		}
	}

	@Override
	public int insert(Connection con, String text, String title,
			String labelID, String cid, String uid) {
		// TODO Auto-generated method stub
		int id=0;
		String sql = "INSERT INTO `collaborative_learning`.`bbs_topic` (`ClassID`,`Title`,`Body`,`CreatedOn`,`UserID`)VALUES(?,?,?,?,?)";
		try {
			PreparedStatement prs = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			prs.setInt(1, Integer.valueOf(cid));
			prs.setString(2, title);
			prs.setString(3, text);
			prs.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
			prs.setInt(5, Integer.valueOf(uid));
			prs.executeUpdate();
			ResultSet rs = prs.getGeneratedKeys();
			if (rs.next()) {
				id = rs.getInt(1);
			}
			return id;
		} catch (SQLException ex) {
			ex.printStackTrace();
			return 0;
		}
	}

	@Override
	public ResultSet queryforUser(Connection con, int start, int end,
			String ID,String state, String order, String sort) {
		// TODO Auto-generated method stub
		String sql = "select t.*,b.StateName,tl.Name as labelName,uc.userNickName as CreatorName,mu.userNickName as ModifierName, ure.userNickName as RepliedName from `collaborative_learning`.`bbs_topic` as t left join(select ts.TopicID,GROUP_CONCAT(tst.Name SEPARATOR ' ') as StateName from `collaborative_learning`.`bbs_topicstate` as ts left join `collaborative_learning`.`bbs_topicstatetype` as tst on tst.ID=ts.StateTypeID group by ts.TopicID) as b on b.TopicID = t.ID LEFT join `collaborative_learning`.`bbs_topiclabel` as tl on tl.ID=t.TopicLabelID left join `collaborative_learning`.`user` as uc on uc.userId=t.UserID left join `collaborative_learning`.`user` as mu on mu.userId=t.UserID left join `collaborative_learning`.`user` as ure on ure.userId=t.RepliedBy ";
		if (!StringUtils.isNullOrEmpty(ID)) {
			sql += " where t.UserID=" + ID;
			if (state != null && !"".equals(state)) {
				sql += " and b.StateName " + state;
			}
		}
		if (!StringUtils.isNullOrEmpty(sort)) {
			sql += " order by " + sort + " ";
			if (order != null) {
				sql += " " + order;
			}
		}
		if (end != 0 && start < end) {
			sql += " limit " + start + "," + end;
		}
		PreparedStatement prs;
		try {
			prs = con.prepareStatement(sql);
			return prs.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("非法请求：" + sql);
			return null;
		}
	}

	@Override
	public int updateHit(Connection con, String find) {
		// TODO Auto-generated method stub
		String sql = "UPDATE `collaborative_learning`.`bbs_topic`SET Hit =Hit+1  WHERE "+find;
		try {
			PreparedStatement prs = con.prepareStatement(sql);
			return prs.executeUpdate();
		} catch (SQLException ex) {
			ex.printStackTrace();
			System.out.println("非法sql语句  "+sql);
			return 0;
		}
	}

}
